package ksj.common;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.JSONArray;
import org.json.JSONObject;

@Controller
@EntityScan(basePackages = { "bean" })
public class ReportController {
	@Autowired
	private DBRepository dbRepository;
	@Autowired
	private ReportDefRepository reportDefRepository;

	@ResponseBody
	@RequestMapping(path = "/tracy")
	public String tracy() {
		return "tracy";
	}

	@GetMapping(path = "/owners")
	public @ResponseBody Iterable<Integer> getAllOwners() {
		System.out.println("got request /owners");
		return dbRepository.getDistinctHubs();
		// return confRepository.getDistinctHubs();
	}

	@GetMapping(path = "/writeExcelwithReportID")
	public @ResponseBody List<Object[]> writeExcelByReportID(@RequestParam String souceExcel, @RequestParam String targetExcel,
			@RequestParam int reportID) {
		System.out.println("asdasd");
		List<Object[]> reports = null;
		Workbook workbook_Souce = null;
		Workbook workbook_Template = null;
		String souceSheetName = null;
		String souceTabName = null;
		try {
			FileInputStream souceFis = new FileInputStream(souceExcel);
			if (souceExcel.toLowerCase().endsWith("xlsx")) {
				workbook_Souce = new XSSFWorkbook(souceFis);
			} else if (souceExcel.toLowerCase().endsWith("xls")) {
				workbook_Souce = new HSSFWorkbook(souceFis);
			}
			int numberOfSheets_Souce = workbook_Souce.getNumberOfSheets();
			reports  = reportDefRepository.getReports(1);
			for (int r=0;r<reports.size();r++) {
				JSONObject definition_json = new JSONObject(reports.get(r)[0].toString()); 
				String report_template = reports.get(r)[1].toString();
				FileInputStream fis = new FileInputStream(report_template);
				
				if (report_template.toLowerCase().endsWith("xlsx")) {
					workbook_Template = new XSSFWorkbook(fis);
				} else if (report_template.toLowerCase().endsWith("xls")) {
					workbook_Template = new HSSFWorkbook(fis);
				}
				int numberOfSheets = workbook_Template.getNumberOfSheets();
				System.out.println("report_templateä¸€å…±" + numberOfSheets + "ä¸ªsheet");
				System.out.println("workbook_Souceä¸€å…±" + numberOfSheets_Souce + "ä¸ªsheet");
				JSONArray tabs = definition_json.getJSONArray("tabs");
				for(int t=0;t<tabs.length();t++){
					JSONObject tab = tabs.getJSONObject(t);
					String tab_name = tab.getString("tab_name");
					String sheet_name = tab.getString("sheet_name");
					int startrow = Integer.parseInt(tab.getJSONObject("xls_position").getString("startrow"));
					int delta = Integer.parseInt(tab.getJSONObject("xls_position").getString("delta"));
					for (int j = 0; j < numberOfSheets_Souce; j++) {
						int rowNbr = startrow;
						souceSheetName = workbook_Souce.getSheetName(j).split("_")[0];
						souceTabName = workbook_Souce.getSheetName(j).split("_")[1];
						if (sheet_name.equals(souceSheetName)&&tab_name.equals(souceTabName)) {
							Sheet souce_sheet = workbook_Souce.getSheetAt(j);
							for (int souce_rowNbr = 0; souce_rowNbr < souce_sheet.getLastRowNum() + 1; souce_rowNbr++){
								int deltaNbr = delta;
								Row souce_row = souce_sheet.getRow(souce_rowNbr);
								for (int souce_cellNbr = 0; souce_cellNbr < souce_row.getLastCellNum(); souce_cellNbr++){
									try{
										String value = souce_row.getCell(souce_cellNbr).toString();
										System.out.print(souce_row.getCell(souce_cellNbr) + "\t");
										Sheet template_sheet = workbook_Template.getSheet(sheet_name);
										if(template_sheet.getRow(rowNbr) == null){
											template_sheet.createRow(rowNbr);
										}
										Row template_row = template_sheet.getRow(rowNbr);
										if(template_row.getCell(deltaNbr) == null){
											template_row.createCell(deltaNbr);
										}
										Cell template_cell = template_row.getCell(deltaNbr);
										template_cell.setCellValue(value);
										deltaNbr += 1;
									}catch(Exception NullPointerException){
										deltaNbr += 1;
//										System.out.println(NullPointerException);
										continue;
									}
								}
//								FileOutputStream out = new FileOutputStream("C:/Users/tracy/Desktop/testExcel/result.xlsx");
//								workbook_Template.write(out);
//								out.flush();
								rowNbr += 1;
							}
						}
					}
					
				}
				
//				Object[] obj = new Object[reports.get(i).length];
//				obj = reports.get(i);
//				
//			    System.out.println(obj[0].toString());
////				JSONObject json = new JSONObject(obj[0].toString()); 
				FileOutputStream out = new FileOutputStream(targetExcel);
				workbook_Template.write(out);
				out.flush();
				System.out.println("end");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("end");
		
		return reports;
	}

	@ResponseBody
	@RequestMapping(path = "/writeExcel")
	public String wasif(@RequestParam String templateExcel, @RequestParam String souceExcel) {
		// String tmpExcel="C:/Users/tracy/Desktop/testExcel/2.åº“å­˜ç®¡ç�†å¿«æŠ¥.xlsx";
		// String southExcel="C:/Users/tracy/Desktop/testExcel/souceExcel.xlsx";

		System.out.println(templateExcel);
		Sheet sheet = null;
		Sheet template_sheet = null;
		try {
			FileInputStream fis = new FileInputStream(templateExcel);
			FileInputStream souceFis = new FileInputStream(souceExcel);
			Workbook workbook_Template = null;
			Workbook workbook_Souce = null;
			if (templateExcel.toLowerCase().endsWith("xlsx")) {
				workbook_Template = new XSSFWorkbook(fis);
			} else if (templateExcel.toLowerCase().endsWith("xls")) {
				workbook_Template = new HSSFWorkbook(fis);
			}
			if (souceExcel.toLowerCase().endsWith("xlsx")) {
				workbook_Souce = new XSSFWorkbook(souceFis);
			} else if (souceExcel.toLowerCase().endsWith("xls")) {
				workbook_Souce = new HSSFWorkbook(souceFis);
			}
			int numberOfSheets = workbook_Template.getNumberOfSheets();
			int numberOfSheets_Souce = workbook_Souce.getNumberOfSheets();
			System.out.println("ä¸€å…±" + numberOfSheets + "ä¸ªsheet");
			for (int i = 0; i < numberOfSheets; i++) {
				for (int j = 0; j < numberOfSheets_Souce; j++) {
					if (workbook_Template.getSheetName(i).equals(workbook_Souce.getSheetName(j))) {
						String sheetName = workbook_Souce.getSheetName(j);
						System.out.println(sheetName);
						sheet = workbook_Souce.getSheetAt(j);
						for (int rowNbr = 0; rowNbr < sheet.getLastRowNum() + 1; rowNbr++) {// getLastRowNum
							// èŽ·å�–æœ€å�Žä¸€è¡Œçš„è¡Œæ ‡
							Row row = sheet.getRow(rowNbr);
							if (row != null) {
								for (int cellNbr = 0; cellNbr < row.getLastCellNum(); cellNbr++) {// getLastCellNum
									// æ˜¯èŽ·å�–æœ€å�Žä¸€ä¸ªä¸�ä¸ºç©ºçš„åˆ—æ˜¯ç¬¬å‡ ä¸ª
									if (row.getCell(cellNbr) != null) { // getCell
																		// èŽ·å�–å�•å…ƒæ ¼æ•°æ�®
										String value = row.getCell(cellNbr).toString();
										System.out.print(row.getCell(cellNbr) + "\t");
										template_sheet = workbook_Template.getSheetAt(i);
										Row template_row = template_sheet.getRow(rowNbr);
										Cell template_cell = template_row.getCell(cellNbr);
										template_cell.setCellValue(value);
									} else {
										System.out.print("\t");
									}
								}
							}
							System.out.println("");
						}
					}
				}
			}

			// Sheet sheet = workbook.getSheetAt(1);
			// System.out.println(sheet.getSheetName()+" sheet");
			//
			// XSSFRow row = (XSSFRow) sheet.getRow(10);
			// Cell cell = row.getCell(0);
			// String value = cell.getStringCellValue();
			// cell.setCellValue("100æœˆ");
			FileOutputStream out = new FileOutputStream("C:/Users/tracy/Desktop/testExcel/result.xlsx");
			workbook_Template.write(out);

			System.out.println("end");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;

	}

	public DBRepository getDbRepository() {
		return dbRepository;
	}

	public void setDbRepository(DBRepository dbRepository) {
		this.dbRepository = dbRepository;
	}
}
